package com.scalar.forecast.forecastEntry.actionCommands;

import com.scalar.forecast.actionCommands.ActionCommand;
import com.scalar.forecast.forecastEntry.forms.ForecastEntryForm;
import com.scalar.forecast.forecastEntry.*;
import com.scalar.forecast.commons.SessionKeys;
import com.scalar.forecast.commons.Constants;
import com.scalar.chiptrack.utils.ConfigManager;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import org.apache.struts.action.ActionForm;
import org.apache.struts.util.LabelValueBean;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.util.CellRangeAddress;


import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import javax.servlet.ServletOutputStream;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.ArrayList;
import java.text.DecimalFormat;

/**
 * User: Sujan
 * Date: Feb 27, 2005
 * Time: 1:13:58 PM
 */
public class ForecastEntryExportToXLSCommand extends ActionCommand
{
    public ActionForward execute(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) throws Exception
    {
        HttpSession session = request.getSession();
        ForecastEntryForm forecastEntryForm = ( ForecastEntryForm )session.getAttribute ( "forecastEntryForm" );
        HashMap info = new HashMap();
        if ( forecastEntryForm == null )
        {
            System.out.println ( "----ForecastEntryForm Couldn't Found in Session" );
            info.put( INVALID_REQUEST, "" );
            request.setAttribute ( SessionKeys.INFO_MESSAGES, info );
            return mapping.findForward ( "blankPage" );
        }
        TableMetaData tableMetaData = forecastEntryForm.getTableMetaData();
        //LinkedList rowDataList = forecastEntryForm.getTableDataList();
		LinkedList rowDataList = forecastEntryForm.getTableDataListDefaultValues ();
        ArrayList searchFieldList = forecastEntryForm.getSearchFieldList();
        int searchFieldListSize = searchFieldList.size();
        int noOfSlots = tableMetaData.getNoOfSlots();
        int columnsInEachSlot = tableMetaData.getNoOfTableColumns();
        RowData rowData = null;
        SlotData slotData = null;
        int rowDataListSize = rowDataList.size();
        int maxLevelOrder = 0;
        if ( rowDataListSize > 0 )
        {
            maxLevelOrder = ( ( RowData )rowDataList.get( 0 ) ).getMaxLevelOrder();
        }
        int headingColSpan = maxLevelOrder + ( noOfSlots * columnsInEachSlot );

        //---------EXCEL CODE STARTS HERE-----------//
        Workbook workbook = null;
        if ("xlsx".equalsIgnoreCase(ConfigManager.getXLSWorkBookFormat() ) )
        {
            workbook    = new XSSFWorkbook();
        }
        else
        {
            workbook    = new HSSFWorkbook();
        }
        CreationHelper ch = workbook.getCreationHelper();
        DataFormat df = ch.createDataFormat();
        Sheet forecastEntrySheet = workbook.createSheet( "Forecast Entry" );
        forecastEntrySheet.setSelected ( true );

        //creating fonts
        Font headerFont = workbook.createFont();
        headerFont.setFontHeightInPoints ( ( short ) 14 );
        headerFont.setBoldweight ( Font.BOLDWEIGHT_BOLD );
        headerFont.setFontName( "verdana" );
        headerFont.setColor ( IndexedColors.BLACK.getIndex() );

        Font  searchFieldFont = workbook.createFont();
        searchFieldFont.setFontHeightInPoints ( ( short ) 10 );
        searchFieldFont.setBoldweight ( Font.BOLDWEIGHT_BOLD );
        searchFieldFont.setFontName( "verdana" );
        searchFieldFont.setColor ( IndexedColors.BLACK.getIndex() );

        Font tableHeadFont = workbook.createFont();
        tableHeadFont.setFontHeightInPoints ( ( short ) 9 );
        tableHeadFont.setBoldweight ( Font.BOLDWEIGHT_BOLD );
        tableHeadFont.setFontName( "verdana" );
        tableHeadFont.setColor ( IndexedColors.WHITE.getIndex() );

        Font valueFont = workbook.createFont();
        valueFont.setFontHeightInPoints ( ( short ) 8 );
        valueFont.setBoldweight ( Font.BOLDWEIGHT_NORMAL );
        valueFont.setFontName( "verdana" );
        valueFont.setColor ( IndexedColors.BLACK.getIndex() );

        //creating cell styles
        CellStyle headerStyle  = workbook.createCellStyle ( );
        headerStyle.setBorderBottom ( CellStyle.BORDER_THIN );
        headerStyle.setBorderTop ( CellStyle.BORDER_THIN );
        headerStyle.setBorderLeft ( CellStyle.BORDER_THIN );
        headerStyle.setBorderRight ( CellStyle.BORDER_THIN );
        headerStyle.setAlignment ( CellStyle.ALIGN_CENTER );
        headerStyle.setWrapText ( true );
        headerStyle.setFont ( headerFont );

        CellStyle searchFieldStyle  = workbook.createCellStyle ( );
        searchFieldStyle.setBorderBottom ( CellStyle.BORDER_THIN );
        searchFieldStyle.setBorderTop ( CellStyle.BORDER_THIN );
        searchFieldStyle.setBorderLeft ( CellStyle.BORDER_THIN );
        searchFieldStyle.setBorderRight ( CellStyle.BORDER_THIN );
        searchFieldStyle.setAlignment ( CellStyle.ALIGN_CENTER );
        searchFieldStyle.setWrapText ( true );
        searchFieldStyle.setFont ( searchFieldFont );

        CellStyle searchFieldValueStyle  = workbook.createCellStyle ( );
        searchFieldValueStyle.setBorderBottom ( CellStyle.BORDER_THIN );
        searchFieldValueStyle.setBorderTop ( CellStyle.BORDER_THIN );
        searchFieldValueStyle.setBorderLeft ( CellStyle.BORDER_THIN );
        searchFieldValueStyle.setBorderRight ( CellStyle.BORDER_THIN );
        searchFieldValueStyle.setAlignment ( CellStyle.ALIGN_CENTER );
        searchFieldValueStyle.setWrapText ( true );
        searchFieldValueStyle.setFont ( valueFont );

        CellStyle tableHeaderStyle  = workbook.createCellStyle ( );
        tableHeaderStyle.setBorderBottom ( CellStyle.BORDER_THIN );
        tableHeaderStyle.setBorderTop ( CellStyle.BORDER_THIN );
        tableHeaderStyle.setBorderLeft ( CellStyle.BORDER_THIN );
        tableHeaderStyle.setBorderRight ( CellStyle.BORDER_THIN );
        tableHeaderStyle.setAlignment ( CellStyle.ALIGN_CENTER );
        tableHeaderStyle.setFillPattern ( CellStyle.SOLID_FOREGROUND );
        tableHeaderStyle.setFillForegroundColor ( IndexedColors.DARK_RED.getIndex() );
        tableHeaderStyle.setWrapText ( true );
        tableHeaderStyle.setFont ( tableHeadFont );

        CellStyle tableSubHeaderStyle  = workbook.createCellStyle ( );
        tableSubHeaderStyle.setBorderBottom ( CellStyle.BORDER_THIN );
        tableSubHeaderStyle.setBorderTop ( CellStyle.BORDER_THIN );
        tableSubHeaderStyle.setBorderLeft ( CellStyle.BORDER_THIN );
        tableSubHeaderStyle.setBorderRight ( CellStyle.BORDER_THIN );
        tableSubHeaderStyle.setAlignment ( CellStyle.ALIGN_CENTER );
        tableSubHeaderStyle.setFillPattern ( CellStyle.SOLID_FOREGROUND );
        tableSubHeaderStyle.setFillForegroundColor ( IndexedColors.TAN.getIndex() );
        tableSubHeaderStyle.setWrapText ( true );
        tableSubHeaderStyle.setFont ( searchFieldFont );

        CellStyle salesPersonNameStyle  = workbook.createCellStyle ( );
        salesPersonNameStyle.setBorderBottom ( CellStyle.BORDER_THIN );
        salesPersonNameStyle.setBorderTop ( CellStyle.BORDER_THIN );
        salesPersonNameStyle.setBorderLeft ( CellStyle.BORDER_THIN );
        salesPersonNameStyle.setBorderRight ( CellStyle.BORDER_THIN );
        salesPersonNameStyle.setAlignment ( CellStyle.ALIGN_CENTER );
        salesPersonNameStyle.setFillPattern ( CellStyle.SOLID_FOREGROUND );
        salesPersonNameStyle.setFillForegroundColor ( IndexedColors.GREY_25_PERCENT.getIndex() );
        salesPersonNameStyle.setWrapText ( true );
        salesPersonNameStyle.setFont ( searchFieldFont );

        CellStyle tableValueStyle  = workbook.createCellStyle ( );
        tableValueStyle.setBorderBottom ( CellStyle.BORDER_THIN );
        tableValueStyle.setBorderTop ( CellStyle.BORDER_THIN );
        tableValueStyle.setBorderLeft ( CellStyle.BORDER_THIN );
        tableValueStyle.setBorderRight ( CellStyle.BORDER_THIN );
        tableValueStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
        tableValueStyle.setFillPattern ( CellStyle.SOLID_FOREGROUND );
        tableValueStyle.setFillForegroundColor ( IndexedColors.GREY_25_PERCENT.getIndex() );
        tableValueStyle.setWrapText ( true );
        tableValueStyle.setFont ( valueFont );



        int currentRowNumber = -1;
        Row row = createRow ( forecastEntrySheet, ++currentRowNumber );
        createCells ( row, 0, headingColSpan );
        Cell cell = mergeCells ( forecastEntrySheet, currentRowNumber, 0, currentRowNumber, (headingColSpan-1), headerStyle );
        String heading = forecastEntryForm.isBuild() ? "Build" : "Sales";
        setCellValue ( cell, ch.createRichTextString(heading + " Forecast"), null );
        createRow ( forecastEntrySheet, ++currentRowNumber );      //leaving a row gap after the heading
        SearchField searchField = null;
        boolean displayInNewRow = true;
        short displayNameStartColumn = 0;
        int searchFieldsCount = 0;
        String showNext = "";
        for ( int i = 0; i < searchFieldListSize; i++ )
        {
            searchField = ( SearchField ) searchFieldList.get ( i );
            if ( searchFieldsCount%2 == 0 )
            {
                displayInNewRow = true;
            }
            if ( searchField.isDisplay() )
            {
                searchFieldsCount++;
                if ( displayInNewRow )
                {
                    row = createRow ( forecastEntrySheet, ++currentRowNumber );
                    displayInNewRow = false;
                    displayNameStartColumn = 0;
                }

                if ( "startMonth_Quarter_Year".equalsIgnoreCase( searchField.getPropertyName() ) )
                {
                    String displayName = searchField.getDisplayName();
                    int startIndex = displayName.indexOf( "Start" );
                    if ( startIndex >= 0 )
                    {
                        showNext = displayName.substring( startIndex+5 ) + "s";
                    }
                    else
                    {
                        showNext = displayName + "s";
                    }
                }
                cell = getCellAt ( row, displayNameStartColumn );
                setCellValue ( forecastEntrySheet, displayNameStartColumn,cell, ch.createRichTextString(searchField.getDisplayName()), searchFieldStyle );
                createCells ( row, displayNameStartColumn+1, 2 );
                cell = mergeCells ( forecastEntrySheet, currentRowNumber, (displayNameStartColumn+1), currentRowNumber, ( displayNameStartColumn + 2 ), searchFieldValueStyle  );
                if ( "Show Next".equalsIgnoreCase( searchField.getDisplayName() ) )
                {
                    setCellValue ( cell, ch.createRichTextString(searchField.getPropertyValue() + " " + showNext ), null );
                }
                else
                {
                    setCellValue ( cell, ch.createRichTextString(searchField.getPropertyValue()), null );
                }
                displayNameStartColumn+=3;
            }
        }
        createRow ( forecastEntrySheet, ++currentRowNumber ); //leaving a row gap after the search fields.

        row = createRow ( forecastEntrySheet, ++currentRowNumber );
        createCells ( row,0, maxLevelOrder );
        cell = mergeCells( forecastEntrySheet, currentRowNumber, 0, currentRowNumber, ( maxLevelOrder-1 ), tableHeaderStyle );
        setCellValue( cell, ch.createRichTextString("Sales Person"), null );
        ArrayList slotNames = tableMetaData.getSlotNames();
        ArrayList tableColumnList = tableMetaData.getTableColumnList();
        int tableColumnListSize = tableColumnList.size();
        int slotStartCellNumber = maxLevelOrder;

        for ( int i = 0; i < noOfSlots; i++ )
        {
            LabelValueBean slotName = ( LabelValueBean )slotNames.get( i );
            createCells ( row, slotStartCellNumber, columnsInEachSlot );
            cell = mergeCells( forecastEntrySheet, currentRowNumber, slotStartCellNumber, currentRowNumber, ( slotStartCellNumber+columnsInEachSlot-1), tableHeaderStyle );
            setCellValue( cell, ch.createRichTextString(slotName.getLabel()), tableHeaderStyle );
            slotStartCellNumber = slotStartCellNumber + columnsInEachSlot;
        }
        row = createRow ( forecastEntrySheet, ++currentRowNumber );
        createCells ( row, 0, maxLevelOrder );
        cell = mergeCells ( forecastEntrySheet, currentRowNumber, 0, currentRowNumber, (maxLevelOrder-1), tableSubHeaderStyle );
        setCellValue ( cell, ch.createRichTextString(""), tableSubHeaderStyle );
        int slotColumnStartIndex = maxLevelOrder;
        TableColumn tableColumn = null;
        for ( int i = 0; i < noOfSlots; i++ )
        {
            for ( int j = 0; j < tableColumnListSize; j++ )
            {
                tableColumn = ( TableColumn )tableColumnList.get ( j );
                if ( tableColumn.isDisplay() )
                {
                    cell = getCellAt( row, slotColumnStartIndex );
                    slotColumnStartIndex++;
                    setCellValue( forecastEntrySheet, (slotColumnStartIndex-1), cell, ch.createRichTextString(tableColumn.getDisplayName()), tableSubHeaderStyle );
                }
            }
        }
        int currentLevel = 0;
        LinkedList slotDataList = null;
        for ( int r = 0; r < rowDataListSize; r++ )
        {
            slotColumnStartIndex = maxLevelOrder;
            rowData = ( RowData )rowDataList.get ( r );
            row = createRow ( forecastEntrySheet, ++currentRowNumber );
            currentLevel = rowData.getLevelOrder();
            for ( int i=0; i < maxLevelOrder; i++ )
            {
                cell = getCellAt ( row, i );
                setCellValue ( cell, ch.createRichTextString(""), salesPersonNameStyle );
            }
            cell = getCellAt( row, ( maxLevelOrder-currentLevel) );
            setCellValue( forecastEntrySheet, ( maxLevelOrder-currentLevel), cell, ch.createRichTextString(rowData.getSalesPersonName()), salesPersonNameStyle );
            slotDataList = rowData.getSlotDataList();
            int slotDataListSize = slotDataList == null ? 0 : slotDataList.size();
            for ( int slot = 0; slot < slotDataListSize; slot++ )
            {
                slotData = ( SlotData )slotDataList.get ( slot );
                for ( int j = 0; j < tableColumnListSize; j++ )
                {
                    tableColumn = ( TableColumn )tableColumnList.get ( j );
                    if ( tableColumn.isDisplay() )
                    {
                        cell = getCellAt ( row, slotColumnStartIndex++ );
                        if ( "Qty".equalsIgnoreCase( tableColumn.getName() ) )
                        {
                            setCellValue( cell, slotData.getQty(), tableValueStyle );
                        }
                        else if ( "Price".equalsIgnoreCase( tableColumn.getName() ) )
                        {
                            setCellValue( cell, slotData.getPrice() , tableValueStyle);
                        }
                        else if ( "Confidence Level".equalsIgnoreCase( tableColumn.getName() ) )
                        {
                            if ( currentLevel == 1 )
                            {
                                setCellValue( cell, slotData.getConfidenceLevel() , tableValueStyle );
                            }
                            else
                            {
                                setCellValue( cell, ch.createRichTextString("") , tableValueStyle );
                            }
                        }
                        else if ( Constants.COLUMN_USERLIST1.equalsIgnoreCase( tableColumn.getName() ) )
                        {
                            setCellValue( cell, ch.createRichTextString(slotData.getUserList1()), tableValueStyle );
                        }
                        else if ( Constants.COLUMN_USERDATE1.equalsIgnoreCase( tableColumn.getName() ) )
                        {
                            setCellValue( cell, ch.createRichTextString(slotData.getUserDate1()), tableValueStyle );
                        }
                        else if ( Constants.COLUMN_USERTXT1.equalsIgnoreCase( tableColumn.getName() ) )
                        {
                            setCellValue( cell, ch.createRichTextString(slotData.getUserTxt1()), tableValueStyle );
                        }
                        else if ( Constants.COLUMN_USERTXT2.equalsIgnoreCase( tableColumn.getName() ) )
                        {
                            setCellValue( cell, ch.createRichTextString(slotData.getUserTxt2()), tableValueStyle );
                        }
                    }
                }
            }
        }

        response.setContentType ( "application/vnd.ms-excel");
        ServletOutputStream out = response.getOutputStream ( );
        workbook.write ( out );
        if ( out != null ){
    	    out.close();
		}
        return mapping.findForward ( "success" );
    }

    /**
     * creats a row in the sheet at the specified rowNumber.
     * @param sheet
     * sheet object in which the row is to be created
     * @param rowNumber
     * rowNumber start from 0.
     * @return
     * returns the created row object.
     */
    private Row createRow (Sheet sheet, int rowNumber )
    {
        Row row = sheet.createRow ( rowNumber );
        return row;
    }

    /**
     * creats cells in the specified row.
     * @param row
     * cellls will be created in this row.
     * @param startCell
     * cell number indicates from where cells to be created<br>
     * starts from 0.
     * @param noOfCells
     * number of cells to be created.
     */
    private void createCells ( Row row, int startCell, int noOfCells )
    {
        for ( int i = 0 ; i < noOfCells; i++ )
        {
            row.createCell( startCell );
            startCell++;
        }
    }

    /**
     * merges cells
     * @param sheet
     * sheet object in which the cells has to be merged.
     * @param startRow
     * start row Number to merge from.<br>
     * starts from 0.
     * @param startCell
     * start cell number in the start row to merge from.<br>
     * starts from 0.
     * @param endRow
     * end row Number<br>
     * starts from 0.
     * @param endCell
     * end cell number<br>
     * starts from 0.
     * @return
     * returns cell object of the startRow, startCell.
     */
    private Cell mergeCells ( Sheet sheet, int startRow, int startCell, int endRow, int endCell )
    {
        sheet.addMergedRegion( new CellRangeAddress( startRow, endRow, startCell, endCell ) );
        return getCellAt ( sheet.getRow( startRow), startCell );
    }

    /**
     * Returns the cell object at the specified cell in the row.<br>
     * It creats cell if doesn't exists.
     * @param row
     * row object from which cell object has to be picked
     * @param cellNumber
     * starts from 0.
     * @return
     */
    private Cell getCellAt ( Row row, int cellNumber )
    {
        Cell cell = null;
        cell = row.getCell( cellNumber );
        if ( cell == null )
        {
            cell = row.createCell ( cellNumber );
        }
        return cell;
    }

    private void setCellValue ( Cell cell, RichTextString value, CellStyle cellStyle )
    {
        if ( cellStyle != null )
        {
            cell.setCellStyle( cellStyle);
        }
        cell.setCellValue( value );
    }

    private void setCellValue ( Cell cell, int value, CellStyle cellStyle )
    {

        cell.setCellValue( value );
        if ( cellStyle != null )
        {
            cell.setCellStyle( cellStyle);
        }

    }
    private void setCellValue ( Cell cell, double value, CellStyle cellStyle )
    {
        DecimalFormat decimalFormat = new DecimalFormat( "############.00" );
        value = Double.parseDouble ( decimalFormat.format( value ) );
        cell.setCellValue( value );
        if ( cellStyle != null )
        {
            cell.setCellStyle( cellStyle);
        }

    }
    private void setCellValue ( Sheet sheet,int columnIdx ,Cell cell, RichTextString value, CellStyle cellStyle )
    {
        if ( cellStyle != null )
        {
            cell.setCellStyle( cellStyle);
        }
        cell.setCellValue( value );
    }

    /**
     * merges cells and sets each cell with the given cellStyle.
     * @param sheet
     * sheet object in which the cells has to be merged.
     * @param startRow
     * start row Number to merge from.<br>
     * starts from 0.
     * @param startCell
     * start cell number in the start row to merge from.<br>
     * starts from 0.
     * @param endRow
     * end row Number<br>
     * starts from 0.
     * @param endCell
     * end cell number<br>
     * starts from 0.
     * @param endCell
     * @return
     * returns cell object of the startRow, startCell.
     */
    private Cell mergeCells ( Sheet sheet, int startRow, int startCell, int endRow, int endCell, CellStyle cellStyle )
    {
        sheet.addMergedRegion( new CellRangeAddress( startRow, endRow, startCell, endCell ) );
        Cell cell = null;
        for ( int rowIdx = startRow; rowIdx <= endRow; rowIdx++ )
        {
            for ( int cellIdx = startCell; cellIdx <= endCell; cellIdx++ )
            {
                cell = getCellAt ( sheet.getRow ( rowIdx ), cellIdx );
                cell.setCellStyle ( cellStyle );
            }
        }
        return getCellAt ( sheet.getRow( startRow), startCell );
    }


}
